package dk.itu.spvc.tourtracker.service;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.google.android.maps.GeoPoint;

import dk.itu.spvc.tourtracker.model.CurrentState;
import dk.itu.spvc.tourtracker.model.Note;
import dk.itu.spvc.tourtracker.model.Point;
import dk.itu.spvc.tourtracker.model.PointType;
import dk.itu.spvc.tourtracker.model.Trip;

public class ServiceLocalSQL extends SQLiteOpenHelper {

	// CONSTANTS --------------------------------------------------------------

	// Debug
	private static final String TAG = "TourTracker";
	private static final boolean D = true;

	// Database Settings

	private static final String DB_NAME = "TourTracker";
	private static final int DB_VERSION = 4;
	private final Context mContext;

	public static ServiceLocalSQL service;

	private SQLiteDatabase db;

	public ServiceLocalSQL(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
		this.mContext = context;
	}

	public static ServiceLocalSQL getService(Context context) {
		if (service != null)
			return service;
		else
			return new ServiceLocalSQL(context);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		db.beginTransaction();

		try {

			db.execSQL("CREATE TABLE trips (" + "trip_remote_id INTEGER, name TEXT" + ");");

			db.execSQL("CREATE TABLE points (" + "trip_id INTEGER REFERENCES trips(rowid) ON UPDATE CASCADE ON DELETE CASCADE," + "longitude DOUBLE," + "latitude DOUBLE," + "type BOOLEAN " + ");");

			db.execSQL("CREATE TABLE notes (" + "point_id INTEGER REFERENCES points(rowid) ON UPDATE CASCADE ON DELETE CASCADE,"
					+ "trip_id INTEGER REFERENCES trips(rowid) ON UPDATE CASCADE ON DELETE CASCADE," + "text TEXT" + ");");

			db.execSQL("CREATE TABLE current_state (" + "trip_id INTEGER REFERENCES trips(rowid) ON UPDATE CASCADE ON DELETE CASCADE," + "state INTEGER" + ");");

			db.setTransactionSuccessful();

		} catch (SQLException e) {
			if (D)
				Log.e(TAG, "ServiceLocalSQL.onCreate: Error creating tables and debug data " + e.toString());
			throw e;
		} finally {
			db.endTransaction();
		}

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

		if (D)
			Log.w(TAG, "ServiceLocalSQL.onUpgrade: Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");

		db.beginTransaction();

		try {

			db.execSQL("DROP TABLE IF EXISTS trips");
			db.execSQL("DROP TABLE IF EXISTS points");
			db.execSQL("DROP TABLE IF EXISTS notes");
			db.execSQL("DROP TABLE IF EXISTS current_state");

			db.setTransactionSuccessful();

		} catch (SQLException e) {
			if (D)
				Log.e(TAG, "ServiceLocalSQL.onUpgrade: Error upgrading tables and debug data " + e.toString());
			throw e;
		} finally {
			db.endTransaction();
		}

		onCreate(db);

	}

	/**
	 * Add a trip to the database
	 * 
	 * @param tripName
	 *            A name of the trip
	 * @param tripRemoteId
	 *            Id links to the remote server trip id
	 * @return the row id of a newly created trip, or -1 if an error occurred
	 */
	public long addTrip(int tripRemoteId, String tripName) {

		long rowId = -1;

		ContentValues map = new ContentValues();
		map.put("trip_remote_id", tripRemoteId);
		map.put("name", tripName);

		try {
			db = getWritableDatabase();
			rowId = db.insert("trips", null, map);
		} catch (SQLException e) {
			Log.e(TAG, "ServiceLocalSQL.addTrip: Error writing new trip " + e.toString());
		} finally {
			if (db.isOpen())
				db.close();
		}

		return rowId;
	}

	public long addTrip(String tripName) {
		return addTrip(0, tripName);
	}
	
	public void updateTrip(int tripId, int newTripRemoteId, String newTripName) {
		
		String _tripId = tripId+""; 
		
		ContentValues map = new ContentValues();
		map.put("trip_remote_id", newTripRemoteId);
		map.put("name", newTripName);
		
		try {
			db = getWritableDatabase();
			db.update("trips", map, "rowid=?", new String[] {_tripId});
		} catch (SQLException e) {
			Log.e(TAG, "ServiceLocalSQL.updateTrip: Error updating trip " + e.toString());
		} finally {
			if (db.isOpen())
				db.close();
		}
		
	}

	/**
	 * Returns all trips
	 * 
	 * @return List of trips, can be empty
	 */
	public List<Trip> getTrips(boolean onlyLocal) {

		List<Trip> trips = new ArrayList<Trip>();

		try {

			Cursor c;
			db = getReadableDatabase();
			
			if(onlyLocal) {
				c = db.query("trips", new String[] { "rowid", "trip_remote_id", "name" }, "trip_remote_id=?", new String[] {"0"}, null, null, "rowid DESC");
			} else {
				c = db.query("trips", new String[] { "rowid", "trip_remote_id", "name" }, null, null, null, null, "rowid DESC");
			}

			while (c.moveToNext()) {

				int tripId = c.getInt(0);
				int tripRemoteId = c.getInt(1);
				String tripName = c.getString(2);
				Trip trip = new Trip(tripId, tripName);
				trip.setTripRemoteId(tripRemoteId);
				trips.add(trip);

			}

			c.close();

		} catch (SQLException e) {
			Log.e(TAG, "ServiceLocalSQL.getTrips: Error getting trips " + e.toString());
		} finally {
			if (db.isOpen())
				db.close();
		}

		return trips;
	}

	/**
	 * Returns a trip with a specified id
	 * 
	 * @param tripId
	 *            An id of the trip
	 * @return a <b>Trip</b> object, or <b>null</b> if trip does not exist.
	 */
	public Trip getTrip(int tripId) {

		Trip trip = null;

		try {

			db = getReadableDatabase();

			String _tripId = tripId + "";
			Cursor c = db.query("trips", new String[] { "rowid", "trip_remote_id", "name" }, "rowid=?", new String[] { _tripId }, null, null, null);

			if (c.moveToNext()) {
		
				int tripRemoteId = c.getInt(1);
				String tripName = c.getString(2);
				trip = new Trip(tripId, tripName);
				trip.setTripRemoteId(tripRemoteId);

			}

			c.close();

		} catch (SQLException e) {
			Log.e(TAG, "ServiceLocalSQL.getTrip: Error getting a trip " + e.toString());
		} finally {
			if (db.isOpen())
				db.close();
		}

		return trip;

	}

	/**
	 * Add a new point to the database
	 * 
	 * @param tripId
	 *            An id of the trip, to which the point belongs
	 * @param longitude
	 *            Point longitude
	 * @param latitude
	 *            Point latitude
	 * @param type
	 *            A type of point (path - true/1, pin - false/0)
	 * @return the row id of a newly created point, or -1 if an error occurred
	 */

	public long addPoint(int tripId, double longitude, double latitude, PointType type) {

		long rowId = -1;

		boolean _type = (type == PointType.PATH) ? true : false;

		ContentValues map = new ContentValues();
		map.put("trip_id", tripId);
		map.put("longitude", longitude);
		map.put("latitude", latitude);
		map.put("type", _type);

		try {
			db = getWritableDatabase();
			rowId = db.insert("points", null, map);
		} catch (SQLException e) {
			Log.e(TAG, "ServiceLocalSQL.addPoint: Error adding new point " + e.toString());
		} finally {
			if (db.isOpen())
				db.close();
		}

		return rowId;

	}

	public void deletePoint(int pointId) {

		try {

			db = getWritableDatabase();

			String _pointId = pointId + "";
			db.delete("points", "rowid=?", new String[] { _pointId });

		} catch (SQLException e) {
			Log.e(TAG, "ServiceLocalSQL.deletePoint: Error deleting point " + e.toString());
		} finally {
			if (db.isOpen())
				db.close();
		}

	}

	public Point getPoint(int pointId) {

		Point point = null;
		String _pointId = pointId + "";

		try {

			db = getReadableDatabase();
			Cursor c = db.query("points", new String[] { "rowid", "trip_id", "longitude", "latitude", "type" }, "rowid=?", new String[] { _pointId }, null, null, null);

			while (c.moveToNext()) {

				int tripId = c.getInt(1);
				double longitude = c.getDouble(2);
				double latitude = c.getDouble(3);
				PointType type = (c.getInt(4) == 1) ? PointType.PATH : PointType.PIN;

				point = new Point(pointId, tripId, longitude, latitude, type);

			}

			c.close();

		} catch (SQLException e) {
			Log.e(TAG, "ServiceLocalSQL.getPoint: Error getting point " + e.toString());
		} finally {
			if (db.isOpen())
				db.close();
		}

		return point;
	}

	/**
	 * Returns a list of recorded points.
	 */
	public List<Point> getPoints(int tripId, PointType type) {

		List<Point> points = new ArrayList<Point>();

		String _tripId = tripId + "";
		String _type = (type == PointType.PATH) ? 1 + "" : 0 + "";

		try {

			db = getReadableDatabase();
			Cursor c = db.query("points", new String[] { "rowid", "trip_id", "longitude", "latitude", "type" }, "trip_id=? AND type=?", new String[] { _tripId, _type }, null, null, "rowid ASC");

			while (c.moveToNext()) {

				int pointId = c.getInt(0);
				double longitude = c.getDouble(2);
				double latitude = c.getDouble(3);

				Point trip = new Point(pointId, tripId, longitude, latitude, type);

				points.add(trip);

			}

			c.close();

		} catch (SQLException e) {
			Log.e(TAG, "ServiceLocalSQL.getPoints: Error getting points " + e.toString());
		} finally {
			if (db.isOpen())
				db.close();
		}

		return points;
	}

	public long addNote(int pointId, int tripId, String text) {

		long rowId = -1;

		ContentValues map = new ContentValues();
		map.put("point_id", pointId);
		map.put("trip_id", tripId);
		map.put("text", text);

		try {
			db = getWritableDatabase();
			rowId = db.insert("notes", null, map);
			Log.e("TourTracker", "RowId of new Note: " + rowId);
		} catch (SQLException e) {
			Log.e(TAG, "ServiceLocalSQL.addNote: Error adding new note " + e.toString());
		} finally {
			if (db.isOpen())
				db.close();
		}

		return rowId;
	}

	public boolean updateNote(int pointId, String text) {

		boolean isUpdated = false;

		String _pointId = pointId + "";
		ContentValues map = new ContentValues();
		map.put("text", text);

		try {
			db = getWritableDatabase();
			db.update("notes", map, "point_id=?", new String[] { _pointId });
			isUpdated = true;
		} catch (SQLException e) {
			Log.e(TAG, "ServiceLocalSQL.updateNote: Error updating note " + e.toString());
		} finally {
			if (db.isOpen())
				db.close();
		}

		return isUpdated;

	}

	public List<Note> getNotes(long pointId) {

		List<Note> notes = new ArrayList<Note>();

		String _pointId = pointId + "";

		try {

			db = getReadableDatabase();
			Cursor c = db.query("notes", new String[] { "rowid", "point_id", "text", "trip_id" }, "point_id=?", new String[] { _pointId, }, null, null, null);

			while (c.moveToNext()) {

				int noteId = c.getInt(0);
				String text = c.getString(2);
				int tripId = c.getInt(3);

				Note note = new Note(tripId, noteId, pointId, text);
				notes.add(note);

			}

			c.close();

		} catch (SQLException e) {
			Log.e(TAG, "ServiceLocalSQL.getNotes: Error getting notes " + e.toString());
		} finally {
			if (db.isOpen())
				db.close();
		}

		return notes;

	}

	/**
	 * Returns a list of recorded points as a GeoPoint list.
	 */
	public List<GeoPoint> getGeoPoints(int tripId, PointType type) {

		List<Point> points = getPoints(tripId, type);
		List<GeoPoint> geoPoints = new ArrayList<GeoPoint>();

		for (Point point : points) {
			geoPoints.add(new GeoPoint((int) (point.getLatitude() * 1E6), (int) (point.getLongitude() * 1E6)));
		}

		return geoPoints;
	}

	/**
	 * Sets current state in local sevice
	 * 
	 * @param tripId
	 *            An id of the trip
	 * @param state
	 *            The state of the trip for e.g. Recording trip, Uploading trip,
	 *            Stopping trip
	 * @return <b>true</b> if the state successfully changed, otherwise
	 *         <b>false</b>
	 */
	public boolean setCurrentState(long tripId, int state) {

		boolean isSet = false;

		try {

			db = getReadableDatabase();

			db.execSQL("DELETE FROM current_state");

			ContentValues map = new ContentValues();
			map.put("trip_id", tripId);
			map.put("state", state);

			db.insert("current_state", null, map);

			isSet = true;

		} catch (SQLException e) {
			Log.e(TAG, "ServiceLocalSQL.setCurrentState: Error setting current state " + e.toString());
		} finally {
			if (db.isOpen())
				db.close();
		}

		return isSet;
	}

	/**
	 * Returns the state of an application. For example if an application
	 * started recording a trip and crashed, then the data can be retrieved back
	 * to the previous state.
	 * 
	 * @return the <b>CurrentState</b> object, or <b>null</b> if the state does
	 *         not exist
	 */
	public CurrentState getCurrentState() {

		CurrentState currentState = null;

		try {

			db = getReadableDatabase();

			Cursor c = db.query("current_state", new String[] { "trip_id", "state" }, "rowid=1", null, null, null, null);

			if (c.moveToNext()) {

				int tripId = c.getInt(0);
				int state = c.getInt(1);
				currentState = new CurrentState(tripId, state);

			}

			c.close();

		} catch (SQLException e) {
			Log.e(TAG, "ServiceLocalSQL.getCurrentState: Error getting current state " + e.toString());
		} finally {
			if (db.isOpen())
				db.close();
		}

		return currentState;

	}

	public void deleteTrips() {

		try {

			db = getReadableDatabase();
			db.execSQL("delete from trips");

		} catch (SQLException e) {
			Log.e(TAG, "ServiceLocalSQL.deleteTrips: Error deleting trips " + e.toString());
		} finally {
			if (db.isOpen())
				db.close();
		}

	}

}
